/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package hyper.market.beans;

import database.DBAccessController;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Mikeno
 */
public final class StockBean {

    private String stockname, storename;
    private int stockdetailid, quantity, threshold, storeid;
    private double costprice, sellingprice;
    private String stockImage;
    private String status;
    private double discount;
    private String expiryDate;

    public StockBean() {
    }

    public StockBean(int stockid, double sellingprice, double discount) {
        setStockdetailid(stockid);
        setSellingprice(sellingprice);
        setDiscount(discount);
    }

    public StockBean(String stockname, double costprice, double sellingprice, int quantity, int threshold, String stockImage, double discount, String expiryDate) {
        setStockname(stockname);
        setCostprice(costprice);
        setSellingprice(sellingprice);
        setQuantity(quantity);
        setThreshold(threshold);
        setStockImage(stockImage);
        setDiscount(discount);
        setExpiryDate(expiryDate);
    }

    private StockBean(int stockdetailid, String stockname, String stockImage, double costprice, double sellingprice, double discount, int threshold, int quantity, String expiryDate, String status, int storeid) {
        setStockdetailid(stockdetailid);
        setStockname(stockname);
        setCostprice(costprice);
        setSellingprice(sellingprice);
        setQuantity(quantity);
        setThreshold(threshold);
        setStockImage(stockImage);
        setStatus(status);
        setDiscount(discount);
        setExpiryDate(expiryDate);
        setStoreid(storeid);
    }

    //stockid, sellingprice, discountAmt, status
    private StockBean(int stockid, double sellingprice, double discountAmt, String status) {
        setStockdetailid(stockid);
        setSellingprice(sellingprice);
        setDiscount(discountAmt);
        setStatus(status);
    }

    private StockBean(int stockid, double cost, double selling, int quantity, int threshold, double discount, String expiryDate, String status, String stockname, String stockimage, String storename) {
        setStockdetailid(stockid);
        setExpiryDate(expiryDate);
        setCostprice(cost);
        setSellingprice(selling);
        setThreshold(threshold);
        setQuantity(quantity);
        setStockname(stockname);
        setDiscount(discount);
        setStatus(status);
        setStockImage(stockimage);
        setStorename(storename);
    }

    public StockBean(int stockid, String name, double cost, double selling, int threshold, int quantity, double discount, String expiry) {
        setStockdetailid(stockid);
        setExpiryDate(expiry);
        setCostprice(cost);
        setSellingprice(selling);
        setThreshold(threshold);
        setQuantity(quantity);
        setStockname(name);
        setDiscount(discount);
    }

    private StockBean(int stockdetailid, String name) {
        setStockdetailid(stockdetailid);
        setStockname(name);
    }

    public ArrayList retrieveStoreStockNames(String jndi, String searchValue, int storeid) {
        DBAccessController db = new DBAccessController(jndi);
        String sql = "select d.stockdetailid, d.stockName from stockdetails d, storestocks s "
                + "where d.stockdetailid = s.stockdetailid and s.storeid = '" + storeid + "' and d.stockName like '%" + searchValue + "%'";
        ArrayList list = new ArrayList();
        StockBean s = null;
        try {
            ResultSet rs = db.execSelectQuery(sql);
            while (rs.next()) {
                s = new StockBean(rs.getInt("stockdetailid"), rs.getString("stockName"));
                list.add(s);
            }
            db.terminate();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return list;
    }

    public final StockBean retrieveOneStockByIdnStorename(String jndi, String storename, int stockid) {
        DBAccessController db = new DBAccessController(jndi);
        String sql = "select d.stockdetailid, d.stockName, d.stockImage, d.costprice, s.sellingprice, s.discount, s.threshold, s.quantity, s.expirydate, s.status, s.storeid "
                + "from stockdetails d, storestocks s "
                + "where d.stockdetailid = s.stockdetailid and s.storeid = (select storeid from store where storename ='" + storename + "')";

        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        StockBean s = null;
        try {
            ResultSet rs = db.execSelectQuery(sql);
            if (rs.next()) {
                s = new StockBean(rs.getInt("stockdetailid"), rs.getString("stockName"), rs.getString("stockImage"), rs.getDouble("costprice"), rs.getDouble("sellingprice"), rs.getDouble("discount"), rs.getInt("threshold"), rs.getInt("quantity"), df.format(rs.getDate("expirydate")), rs.getString("status"), rs.getInt("storeid"));
            }
            db.terminate();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return s;
    }
    //Function starts here

    public ArrayList searchStockByName(String value, int storeid, String jndi) {
        String sql;
        DBAccessController db = new DBAccessController(jndi);
        if (value.equals("search") || value.equals("") || value == null) {
            sql = "select d.stockdetailid, d.stockName, d.stockImage, d.costprice, s.sellingprice, s.discount, s.threshold, s.quantity, s.expirydate, s.status, s.storeid "
                    + "from stockdetails d, storestocks s "
                    + "where d.stockdetailid = s.stockdetailid and s.storeid ='" + storeid + "'";
        } else {
            sql = "select d.stockdetailid, d.stockName, d.stockImage, d.costprice, s.sellingprice, s.discount, s.threshold, s.quantity, s.expirydate, s.status, s.storeid "
                    + "from stockdetails d, storestocks s "
                    + "where d.stockdetailid = s.stockdetailid and d.stockName LIKE '%" + value + "%' and s.storeid ='" + storeid + "'";
        }

        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

        ArrayList list = new ArrayList();
        StockBean s = null;
        try {
            ResultSet rs = db.execSelectQuery(sql);
            while (rs.next()) {
                s = new StockBean(rs.getInt("stockdetailid"), rs.getString("stockName"), rs.getString("stockImage"), rs.getDouble("costprice"), rs.getDouble("sellingprice"), rs.getDouble("discount"), rs.getInt("threshold"), rs.getInt("quantity"), df.format(rs.getDate("expirydate")), rs.getString("status"), rs.getInt("storeid"));
                list.add(s);
            }
            db.terminate();
        } catch (SQLException e) {

            e.printStackTrace();
            return null;
        }


        return list;
    }
// Done! Fixed on 18/09/11

    public void updateItemPrice(String jndi, StockBean stock, int storeid) {
        DBAccessController db = new DBAccessController(jndi);
        String sql = "update storestocks set Sellingprice ='" + stock.getSellingprice() + "', Discount ='" + stock.getDiscount() + "' Where stockdetailid='" + stock.getStockdetailid() + "' and storeid = '" + storeid + "'";
        db.execUpdateQuery(sql);
        db.terminate();
    }

    public void updateItemDetails(String jndi, StockBean stock, int storeid) {
        DBAccessController db = new DBAccessController(jndi);
        String sql = "update storestocks set Discount ='" + stock.getDiscount() + "', Threshold='" + stock.getThreshold() + "', ExpiryDate ='" + stock.getExpiryDate() + "', status ='" + stock.calculateStockStatus(stock.getQuantity(), stock.getThreshold()) + "' Where stockdetailid='" + stock.getStockdetailid() + "' and storeid = '" + storeid + "'";
        db.execUpdateQuery(sql);

        sql = "update stockdetails set stockName ='" + stock.getStockname() + "', costprice ='" + stock.getCostprice() + "',Usualprice ='" + stock.getSellingprice() + "' where stockdetailid ='" + stock.getStockdetailid() + "'";
        db.execUpdateQuery(sql);
        db.terminate();
    }

    public String calculateStockStatus(int quantity, int threshold) {

        if (quantity >= threshold) {
            return "Available";
        } else if (quantity == 0) {
            return "Out Of Stock";
        } else {
            return "Low In Stock";
        }

    }
//fixed!

    public ArrayList createStock(String jndi, String[] storeid, StockBean stock) {
        DBAccessController db = new DBAccessController(jndi);
        String sql = "";
        ArrayList list = new ArrayList();
        sql = "insert into stockdetails (stockName, stockImage, costprice,usualprice) values ('" + stock.getStockname() + "','" + stock.getStockImage() + "','" + stock.getCostprice() + "', '" + stock.getSellingprice() + "')";
        int result = db.execUpdateQuery(sql), result2 = 0;
        sql = "select distinct last_insert_id() from stockdetails";
        try {
            ResultSet rs = db.execSelectQuery(sql);
            if (rs.next()) {
                for (int i = 0; i < storeid.length; i++) {
                    sql = "insert into storestocks values(last_insert_id(), '" + storeid[i] + "', '" + stock.getSellingprice() + "', '" + stock.getDiscount() + "', '" + stock.getThreshold() + "', '" + stock.getQuantity() + "', '" + stock.getExpiryDate() + "', '" + calculateStockStatus(stock.getQuantity(), stock.getThreshold()) + "')";
                    result2 = db.execUpdateQuery(sql);
                    if (result < 0 || result2 < 0) {
                        list.add(storeid[i]);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


        db.terminate();
        return list;
    }
    // note jndi2 is not added in yet
    // add expiry date into the calculation

    public ArrayList EndOfDayCalculation(String jndi, String jndi2, ArrayList storelist, int staffid) {
        //retrieve data from the sales transaction, reduce the quantity then do this
        // generate statistic graph
        reduceStockQuantity(jndi);
        DBAccessController db = new DBAccessController(jndi);
        double discount = 0;
        double sellingprice = 0;
        String status = "";
        String sql = "";
        StockBean s = null;
        ArrayList initialList = new ArrayList();
        ArrayList updatedList = new ArrayList();
        ArrayList endofdayList = new ArrayList();
        StockBean up = null;
        Calendar currentDate = Calendar.getInstance();
        String myFormatString = "dd-MM-yyyy"; // for example
        SimpleDateFormat df = new SimpleDateFormat(myFormatString);
        Date expirydate = null;
        Date today = currentDate.getTime();
        currentDate.add(Calendar.DATE, +14);
        Date expirywarningdate = currentDate.getTime();
        System.out.println("expiry warning date : " + expirywarningdate);
        for (int i = 0; i < storelist.size(); i++) {
            // never take out stockname , storename
            sql = "select d.stockdetailid, d.costprice, d.usualprice, st.quantity, st.threshold, st.discount, st.Expirydate, st.status, d.stockname, d.stockimage, s.storename from storestocks st ,stockdetails d , store s where st.stockdetailid = d.stockdetailid and s.storeid = st.storeid and st.storeid = '" + ((StoreBean) storelist.get(i)).getStoreid() + "' and st.status != 'Discontinued' ORDER BY s.storename ASC";
            try {
                ResultSet rs = db.execSelectQuery(sql);
                while (rs.next()) {
                    s = new StockBean(rs.getInt(1), rs.getDouble(2), rs.getDouble(3), rs.getInt(4), rs.getInt(5), rs.getDouble(6), df.format(rs.getDate(7)), rs.getString(8), rs.getString("stockname"), rs.getString("stockimage"), rs.getString("storename"));
                    initialList.add(s);
                    try {
                        // try to convert
                        expirydate = df.parse(s.getExpiryDate());

                    } catch (ParseException ex) {
                        Logger.getLogger(StockBean.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    // if expirydate is 2 weeks  warning

                    if (expirydate.before(expirywarningdate) && expirydate.after(today)) {
                        createNotificationExpiryDate(db, s, staffid, ((StoreBean) storelist.get(i)).getStoreid());
                    }
                    //check if quantity is low
                    //if low create notification
                    if (s.getQuantity() < s.getThreshold()) {
                        createNotificationLowStock(db, s, staffid, ((StoreBean) storelist.get(i)).getStoreid());
                    }
                    discount = calculateDiscount(s.getQuantity(), s.getThreshold(), s.getDiscount(), expirydate, expirywarningdate);
                    sellingprice = calculateSelling(s, discount);
                    status = calculateStockStatus(s.getQuantity(), s.getThreshold());
                    //stockid, sellingprice, discountAmt, status
                    //things that change
                    up = new StockBean(s.getStockdetailid(), sellingprice, (discount * 100), status);
                    updatedList.add(up);
                    //a list of updated values
                    updateStockValues(db, jndi2,((StoreBean) storelist.get(i)).getStoreid(), s.getStockdetailid(), sellingprice, status,s.getQuantity());
                }

            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            createReportNotification(db, staffid, ((StoreBean) storelist.get(i)).getStoreid());
        }
        db.terminate();
        endofdayList.add(initialList);
        endofdayList.add(updatedList);
        return endofdayList;

    }

    private void reduceStockQuantity(String jndi) {

        String sql = "select stockid, quantity, storeid from salestransaction where Status ='New'";
        DBAccessController db = new DBAccessController(jndi);
        int tempHolder=0;
        try {
            ResultSet rs = db.execSelectQuery(sql);
            while (rs.next()) {
                 tempHolder = rs.getInt("stockid");
                 db.execUpdateQuery("update storestocks set quantity = quantity - '"+rs.getInt("quantity")+"' where stockdetailid = '"+tempHolder+"' and storeid ='"+rs.getInt("storeid")+"'");
                 db.execUpdateQuery("update salestransaction set Status = 'Updated' where stockid = '"+tempHolder+"' and storeid ='"+rs.getInt("storeid")+"'");
            }
        } catch (SQLException e) {
            e.printStackTrace();

        }
        db.terminate();
    }

    private void createReportNotification(DBAccessController db, int staffid, int storeid) {
        String desc = "A New Cost Has Been Created.";
        String sql = "insert into notification (createdBy,Description,createdDate,action,storeid,stockdetailid) values"
                + "('" + staffid + "','" + desc + "',curdate(),'5','" + storeid + "','0')";
        db.execUpdateQuery(sql);
    }

    private void updateStockValues(DBAccessController db, String jndi2, int storeid, int stockdetailid, double selling, String status, int quantity) {
        String sql = "update storestocks set sellingprice = '" + selling + "' , status ='" + status + "' where stockdetailid = '" + stockdetailid + "' and storeid=" + storeid;
        db.execUpdateQuery(sql);
        DBAccessController db2 = new DBAccessController(jndi2);
        sql = "update storestock set sellingprice = '" + selling + "' , status ='" + status + "',  quantity = '"+quantity+"'  where stockdetailid = '" + stockdetailid + "' and storeid ='"+storeid+"'";
        db2.execUpdateQuery(sql);
        db2.terminate();
    }
    // check quantity is how many * more then threshold
    // each times. * discount = selling
    //  if selling is lower then cost. set costprice
    // if expirydate is near, increase the discount rate

    private double calculateDiscount(double quantity, int threshold, double discount, Date expirydate, Date warningDate) {
        int times = (int) Math.floor(quantity / threshold);
        if (expirydate.after(warningDate)) {
            times++;
        }
        return (times * discount) / 100;
    }

    private double calculateSelling(StockBean stock, double discount) {
        double new_selling = Round(stock.getSellingprice() * (1 - discount), 2);
        if (new_selling < stock.getCostprice()) {
            new_selling = stock.getCostprice();  // if selling price is lower then cost price
        }
        return new_selling;
    }

    public static double Round(double Rval, int Rpl) {
        double p = (double) Math.pow(10, Rpl);
        Rval = Rval * p;
        double tmp = Math.round(Rval);
        return (double) tmp / p;
    }

    private void createNotificationLowStock(DBAccessController db, StockBean stock, int staffid, int storeid) {
        String desc = stock.getStockname() + " is current low in Stock, please purchase more.";
        String sql = "";
        sql = "insert into notification (createdBy,Description,createdDate,action,storeid,stockdetailid) values"
                + "('" + staffid + "','" + desc + "',curdate(),'1','" + storeid + "','" + stock.getStockdetailid() + "')";
        db.execUpdateQuery(sql);
    }

    private void createNotificationExpiryDate(DBAccessController db, StockBean stock, int staffid, int storeid) {
        String desc = "Product " + stock.getStockname() + " is going to expire on : " + stock.getExpiryDate();
        String sql = "";
        sql = "insert into notification (createdBy,Description,createdDate,action,storeid,stockdetailid) values"
                + "('" + staffid + "','" + desc + "',curdate(),'3','" + storeid + "','" + stock.getStockdetailid() + "')";
        db.execUpdateQuery(sql);

        sql = "insert into notification (createdBy,Description,createdDate,action,storeid,stockdetailid) values"
                + "('" + staffid + "','" + desc + "',curdate(),'6','" + storeid + "','" + stock.getStockdetailid() + "')";
        db.execUpdateQuery(sql);

    }

    public boolean updateStatusOfGoods(String jndi, int stockid, String storename, int action) {
        DBAccessController db = new DBAccessController(jndi);
        String sql = "";
        if (action == 1) {
            sql = "update storestocks set status = 'Ordering'  where stockdetailid = '" + stockid + "' and storeid =  (select storeid from store where storename ='" + storename + "')";
        } else if (action == 2) {
            sql = "update storestocks set status = 'Discontinued'  where stockdetailid = '" + stockid + "' and storeid =  (select storeid from store where storename ='" + storename + "')";
        }
        try {
            db.execUpdateQuery(sql);
        } catch (Exception e) {
            db.terminate();
            return false;
        }
        db.terminate();
        return true;

    }

    public ArrayList retrieveStockReport(String jndi, int storeid) {
        DBAccessController db = new DBAccessController(jndi);
        ArrayList list = new ArrayList();
        String sql = "select d.stockdetailid, d.stockName, d.stockImage, d.costprice, s.sellingprice, s.discount, s.threshold, s.quantity, s.expirydate, s.status, s.storeid "
                + "from stockdetails d, storestocks s "
                + "where d.stockdetailid = s.stockdetailid and s.storeid = '" + storeid + "'";
        DateFormat df = new SimpleDateFormat("dd-MMM-yyyy");
        StockBean s = null;
        try {
            ResultSet rs = db.execSelectQuery(sql);
            while (rs.next()) {
                s = new StockBean(rs.getInt("stockdetailid"), rs.getString("stockName"), rs.getString("stockImage"), rs.getDouble("costprice"), rs.getDouble("sellingprice"), rs.getDouble("discount"), rs.getInt("threshold"), rs.getInt("quantity"), df.format(rs.getDate("expirydate")), rs.getString("status"), rs.getInt("storeid"));
                list.add(s);
            }
            db.terminate();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return list;

    }

    public StockBean retrieveExpiryStock(String jndi, String storename, int stockdetailid) {
        DBAccessController db = new DBAccessController(jndi);
        String sql = "select d.stockdetailid, d.stockName, d.stockImage, d.costprice, s.sellingprice, s.discount, s.threshold, s.quantity, s.expirydate, s.status, s.storeid "
                + "from stockdetails d, storestocks s "
                + "where d.stockdetailid = s.stockdetailid and s.stockdetailid ='" + stockdetailid + "' and s.storeid = (select storeid from store where storename ='" + storename + "')";
        DateFormat df = new SimpleDateFormat("dd-MMM-yyyy");
        StockBean s = null;
        try {
            ResultSet rs = db.execSelectQuery(sql);
            if (rs.next()) {
                s = new StockBean(rs.getInt("stockdetailid"), rs.getString("stockName"), rs.getString("stockImage"), rs.getDouble("costprice"), rs.getDouble("sellingprice"), rs.getDouble("discount"), rs.getInt("threshold"), rs.getInt("quantity"), df.format(rs.getDate("expirydate")), rs.getString("status"), rs.getInt("storeid"));

            }
            db.terminate();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return s;

    }

    // connect to gillian
    /**
     * @return the stockname
     */
    public String getStockname() {
        return stockname;
    }

    /**
     * @param stockname the stockname to set
     */
    public void setStockname(String stockname) {
        this.stockname = stockname;
    }

    /**
     * @return the quantity
     */
    public int getQuantity() {
        return quantity;
    }

    /**
     * @param quantity the quantity to set
     */
    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }

    /**
     * @return the threshold
     */
    public int getThreshold() {
        return threshold;
    }

    /**
     * @param threshold the threshold to set
     */
    public void setThreshold(int threshold) {
        this.threshold = threshold;
    }

    /**
     * @return the costprice
     */
    public double getCostprice() {
        return costprice;
    }

    /**
     * @param costprice the costprice to set
     */
    public void setCostprice(double costprice) {
        this.costprice = costprice;
    }

    /**
     * @return the sellingprice
     */
    public double getSellingprice() {
        return sellingprice;
    }

    /**
     * @param sellingprice the sellingprice to set
     */
    public void setSellingprice(double sellingprice) {
        this.sellingprice = sellingprice;
    }

    /**
     * @return the imageStock
     */
    public String getStockImage() {
        return stockImage;
    }

    /**
     * @param imageStock the imageStock to set
     */
    public void setStockImage(String stockImage) {
        this.stockImage = stockImage;
    }

    /**
     * @return the status
     */
    public String getStatus() {
        return status;
    }

    /**
     * @param status the status to set
     */
    public void setStatus(String status) {
        this.status = status;
    }

    /**
     * @return the discount
     */
    public double getDiscount() {
        return discount;
    }

    /**
     * @param discount the discount to set
     */
    public void setDiscount(double discount) {
        this.discount = discount;
    }

    /**
     * @return the storeid
     */
    public int getStoreid() {
        return storeid;
    }

    /**
     * @param storeid the storeid to set
     */
    public void setStoreid(int storeid) {
        this.storeid = storeid;
    }

    /**
     * @return the storename
     */
    public String getStorename() {
        return storename;
    }

    /**
     * @param storename the storename to set
     */
    public void setStorename(String storename) {
        this.storename = storename;
    }

    /**
     * @return the expiryDate
     */
    public String getExpiryDate() {
        return expiryDate;
    }

    /**
     * @param expiryDate the expiryDate to set
     */
    public void setExpiryDate(String expiryDate) {
        this.expiryDate = expiryDate;
    }

    /**
     * @return the stockdetailid
     */
    public int getStockdetailid() {
        return stockdetailid;
    }

    /**
     * @param stockdetailid the stockdetailid to set
     */
    public void setStockdetailid(int stockdetailid) {
        this.stockdetailid = stockdetailid;
    }
}
